import ExcelJS from 'exceljs';
import {saveAs} from 'file-saver'; // 导入 file-saver

// 将列索引转换为 Excel 列字母
const getColumnLetter = (index) => {
    let result = '';
    let num = index;
    while (num > 0) {
        num -= 1;
        const remainder = num % 26;
        result = String.fromCharCode(65 + remainder) + result;
        num = Math.floor(num / 26);
    }
    return result;
};

// 导出 Excel
const exportAsExcel = ({list, columns, filename, sumRow = false}) => {
    // 创建一个工作簿
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet(filename || 'Sheet1');
    // 获取 columns 的项数
    const columnCount = Object.keys(columns).length;

    const titleRow = sheet.addRow([filename]);
    titleRow.font = {bold: true, size: 18}
    titleRow.alignment = {horizontal: 'center', vertical: 'middle'};
    // 合并第一行单元格（横向合并）
    const mergeRangeFirstRow = `A1:${getColumnLetter(columnCount)}1`;
    // const mergeRangeFirstRow = `A1:G1`;
    sheet.mergeCells(mergeRangeFirstRow);

    // 根据 columns 生成表头 bn
    const headers = columns.map((column) => column.title);
    // 添加表头
    const headerRow = sheet.addRow(headers);
    headerRow.font = {bold: true}

    /*   ---  formula ---- start  ---  */
    /*  columns配置formula项如：formula: "SUM({{ddycj}},{{ygscj}})"，则设置其为Excel对应公式    */
    const getLetterByKey = (key) => {
        const index = columns.findIndex(item => {
            return item.dataIndex === key
        })
        if (index < 0) console.error("找不到键为：" + key + "的项");
        return getColumnLetter(index + 1);
    }
    const regex = /\{\{(\w+)}}/g;
    columns.forEach((item) => {
        // 设置公式
        const formula = item.formula;
        if (formula) {
            item["_formula"] = formula.replace(regex, (match, key) => {
                return getLetterByKey(key) + "{{rowIndex}}";
            });
        }
    })

    // 表头行数
    const headerRowCount = 2;
    // 添加数据行
    list.forEach((item, index) => {
        const row = columns.map((column) => {
            if (column._formula) {
                const formula = column._formula;
                return {formula: formula.replace(/\{\{rowIndex}}/g, index + 1 + headerRowCount)};
            }
            let value = item[column.dataIndex];
            const render = column.excelRender || column.render;
            if (render) {
                value = render(value, item);
            }

            if (sumRow && !column.sumName && column.sumName !== "" && value !== null) {
                return Number(value)
            }
            return value;

        });
        sheet.addRow(row);
    });

    // 设置合计行
    if (sumRow) {
        const row = list.length + headerRowCount + 1;
        columns.forEach((column, index) => {
            const cell = sheet.getCell(getColumnLetter(index + 1) + row);
            if (column.sumName) {
                cell.value = column.sumName;
            } else if (column._formula) {
                const formula = column._formula;
                cell.value = {formula: formula.replace(/\{\{rowIndex}}/g, row)};
            } else {
                // 所在列
                const col = getColumnLetter(index + 1);
                cell.value = {formula: `=SUM(${col}${headerRowCount + 1}:${col}${row - 1})`};
            }
        })
    }

    // 需要赋值后设置。
    columns.forEach((column, i) => {
        const {numFmt, excelFont} = column;
        // 设置格式，比如：“0.00%”，
        if (numFmt) {
            sheet.getColumn(i + 1).eachCell((cell) => {
                cell.numFmt = numFmt;
            });
        }

        // 设置字体
        if (excelFont) {
            list.forEach((item, j) => {
                const cell = sheet.getCell(getColumnLetter(i + 1) + (j + headerRowCount + 1));
                const font = excelFont(item[column.dataIndex], item);
                cell.font = {
                    ...cell.font,
                    ...font
                }
            })
        }
    })

    // 冻结第一行和第二行
    sheet.views = [
        {
            state: 'frozen',
            xSplit: 0, // 水平方向不冻结
            ySplit: headerRowCount, // 垂直方向冻结前两行
            topLeftCell: 'A' + (headerRowCount + 1), // 从 A3 开始滚动
            activeCell: 'A' + (headerRowCount + 1), // 活动单元格
        }
    ];

    // 使用 FileSaver.js 保存文件
    workbook.xlsx.writeBuffer().then((data) => {
        const blob = new Blob([data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
        saveAs(blob, `${filename || "导出文档"}.xlsx`);
    }).catch((err) => {
        // todo 由于在promise里被引用，catch时error未打印。
        console.error('保存文件时出错:', err);
    });

};

export default exportAsExcel;